﻿Imports DTO
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Data.SqlClient
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop

Public Class PinDAO
    Private ketnoi As New ConnectDB
    Public Shared Sub ImportExcel(ByVal path As String)
        Dim SheetList As New ArrayList
        Dim objExcel As Excel.Application
        Dim objWorkBook As Excel.Workbook
        Dim objWorkSheets As Excel.Worksheet
        Dim ExcelSheetName As String = ""

        objExcel = CreateObject("Excel.Application")
        objWorkBook = objExcel.Workbooks.Open(path)

        For Each objWorkSheets In objWorkBook.Worksheets
            SheetList.Add(objWorkSheets.Name)
        Next

        'Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = " + path + " ;Extended Properties=""Excel 11.0 Xml;HDR=Yes""")
        Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & path & "';Extended Properties=Excel 8.0;")
        ExcelConnection.Open()
        Dim expr As String = "select * from [" & SheetList(0) & "$]"
        Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
        Dim objDR As OleDbDataReader
        Dim data As New DataProvider()
        data.KetNoiCSDL()
        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(data.connstr)
            bulkCopy.DestinationTableName = "PIN"

            Try
                objDR = objCmdSelect.ExecuteReader
                bulkCopy.WriteToServer(objDR)
                objDR.Close()
                data.DongKetNoi()

            Catch ex As Exception
                MsgBox("Dữ liệu không đúng định dạng")
            End Try
        End Using
    End Sub
    Public Shared Function ReadData() As List(Of PinDTO)
        Dim ketqua As New List(Of PinDTO)
        Dim data As New DataProvider()
        data.KetNoiCSDL()
        Dim command As New SqlCommand()
        command.Connection = data.connectionary
        command.CommandText = "SELECT * FROM PIN"
        Dim dt As System.Data.DataTable = DataProvider.ExecQuery(command)
        For Each row As DataRow In dt.Rows
            Dim pin As New PinDTO(row.Item(0), row.Item(1), row.Item(2), row.Item(3), row.Item(4), row.Item(5), row.Item(6), row.Item(7), row.Item(8), row.Item(9))
            ketqua.Add(pin)
        Next
        data.DongKetNoi()
        Return ketqua
    End Function
    Public Shared Function ReadDataTable() As System.Data.DataTable
        Dim data As New DataProvider()
        data.KetNoiCSDL()
        Dim command As New SqlCommand()
        command.Connection = data.connectionary
        command.CommandText = "SELECT * FROM PIN"
        Dim dt As System.Data.DataTable = DataProvider.ExecQuery(command)
        Return dt
    End Function
    Public Shared Function LoaiPin() As DataSet
        Dim data As New DataProvider()
        data.KetNoiCSDL()
        Dim strSQL As String = "SELECT * FROM LOAIPIN"
        Dim da As New SqlDataAdapter(strSQL, data.connstr)
        Dim ds As New DataSet
        da.Fill(ds, "LoaiPin")
        data.DongKetNoi()
        Return ds
    End Function
    Public Sub InsertData(ByVal tenpin As String, ByVal maloai As Integer, ByVal congsuat As Integer, ByVal kichthuoc As String, ByVal hangsx As String, ByVal imp As Integer, ByVal vmp As Integer, ByVal isc As Integer, ByVal voc As Integer)
        Dim data As New DataProvider()
        data.KetNoiCSDL()
        Dim command As New SqlCommand()
        command.Connection = data.connectionary
        command.CommandText = "INSERT INTO PIN (TenPin,MaLoai,CongSuat,KichThuoc,HangSanXuat,Imp,Vmp,Isc,Voc) VALUES (@TenPin,@MaLoai,@CongSuat,@KichThuoc,@HangSanXuat,@Imp,@Vmp,@Isc,@Voc)"
        Try
            command.Parameters.AddWithValue("@TenPin", tenpin)
            command.Parameters.AddWithValue("@MaLoai", maloai)
            command.Parameters.AddWithValue("@CongSuat", congsuat)
            command.Parameters.AddWithValue("@KichThuoc", kichthuoc)
            command.Parameters.AddWithValue("@HangSanXuat", hangsx)
            command.Parameters.AddWithValue("@Imp", imp)
            command.Parameters.AddWithValue("@Vmp", vmp)
            command.Parameters.AddWithValue("@Isc", isc)
            command.Parameters.AddWithValue("@Voc", voc)

            command.ExecuteNonQuery()
            data.DongKetNoi()
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
            data.DongKetNoi()
        Finally
            data.DongKetNoi()
        End Try
    End Sub
    Public Sub DeleteData(ByVal mapin As Integer)
        Dim data As New DataProvider()
        data.KetNoiCSDL()
        Dim command As New SqlCommand()
        command.Connection = data.connectionary
        command.CommandText = "DELETE FROM PIN  WHERE MaPin = @MaPin"
        'command.CommandText = "DELETE FROM PIN WHERE (TenPin = @TenPin AND MaLoai = @MaLoai AND CongSuat = @CongSuat AND KichThuoc = @KichThuoc AND HangSanXuat = @HangSanXuat AND Imp = @Imp AND Vmp = @Vmp AND Isc = @Isc AND Voc = @Voc)"
        Try
            'command.Parameters.AddWithValue("@TenPin", tenpin)
            'command.Parameters.AddWithValue("@MaLoai", maloai)
            'command.Parameters.AddWithValue("@CongSuat", congsuat)
            'command.Parameters.AddWithValue("@KichThuoc", kichthuoc)
            'command.Parameters.AddWithValue("@HangSanXuat", hangsx)
            'command.Parameters.AddWithValue("@Imp", imp)
            'command.Parameters.AddWithValue("@Vmp", vmp)
            'command.Parameters.AddWithValue("@Isc", isc)
            'command.Parameters.AddWithValue("@Voc", voc)
            command.Parameters.AddWithValue("@MaPin", mapin)
            command.ExecuteNonQuery()
            data.DongKetNoi()
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
            data.DongKetNoi()
        Finally
            data.DongKetNoi()
        End Try
    End Sub
    Public Sub UpdateData(ByVal mapin As Integer, ByVal tenpin As String, ByVal maloai As Integer, ByVal congsuat As Integer, ByVal kichthuoc As String, ByVal hangsx As String, ByVal imp As Integer, ByVal vmp As Integer, ByVal isc As Integer, ByVal voc As Integer)
        Dim data As New DataProvider()
        data.KetNoiCSDL()
        Dim UpdatePayments As String = " UPDATE PIN SET TenPin = @TenPin, MaLoai = @MaLoai, CongSuat = @CongSuat, KichThuoc = @KichThuoc, HangSanXuat = @HangSanXuat, Imp = @Imp, Vmp = @Vmp, Isc = @Isc, Voc = @Voc WHERE MaPin = @MaPin "
        'Dim Updatepayments As String = "UPDATE PIN " & _
        '                               "SET Pin.TenPin = @TenPin, " & _
        '                               "Pin.MaLoai = @MaLoai, " & _
        '                               "Pin.CongSuat = @CongSuat, " & _
        '                               "Pin.KichThuoc = @KichThuoc, " & _
        '                               "Pin.HangSanXuat = @HangSanXuat, " & _
        '                               "Pin.Imp = @Imp, " & _
        '                               "Pin.Vmp = @Vmp, " & _
        '                               "Pin.Isc = @Isc, " & _
        '                               "Pin.Voc = @Voc " & _
        '                               "WHERE Pin.MaPin = @MaPin "

        Dim command As New SqlCommand(UpdatePayments, data.connectionary)
        command.Parameters.AddWithValue("@TenPin", tenpin)
        command.Parameters.AddWithValue("@MaLoai", maloai)
        command.Parameters.AddWithValue("@CongSuat", congsuat)
        command.Parameters.AddWithValue("@KichThuoc", kichthuoc)
        command.Parameters.AddWithValue("@HangSanXuat", hangsx)
        command.Parameters.AddWithValue("@Imp", imp)
        command.Parameters.AddWithValue("@Vmp", vmp)
        command.Parameters.AddWithValue("@Isc", isc)
        command.Parameters.AddWithValue("@Voc", voc)
        command.Parameters.AddWithValue("@MaPin", mapin)

        command.ExecuteNonQuery()
        data.DongKetNoi()

    End Sub
    Public Function TimKiem(ByVal pin As PinDTO)
        Dim sqlString As String
        Dim MaPin As Integer = pin.MaPin
        Dim TenPin As String = pin.TenPin
        Dim MaLoai As Integer = pin.MaLoai
        Dim HangSx As String = pin.HangSanXuat
        Dim CongSuat As Integer = pin.CongSuat
        Dim KichThuoc As String = pin.KichThuoc
        Dim Imp As Integer = pin.Imp
        Dim Vmp As Integer = pin.Vmp
        Dim Isc As Integer = pin.Isc
        Dim Voc As Integer = pin.Voc
        Dim ImpMin As Integer = pin.ImpMin
        Dim ImpMax As Integer = pin.ImpMax
        Dim VmpMin As Integer = pin.VmpMin
        Dim VmpMax As Integer = pin.VmpMax
        Dim IscMin As Integer = pin.IscMin
        Dim IscMax As Integer = pin.IscMax
        Dim VocMin As Integer = pin.VocMin
        Dim VocMax As Integer = pin.VocMax

        If pin.MaPin <> 0 Then
            sqlString = "Select * From PIN WHERE MaPin = " + pin.MaPin.ToString() + " "
        Else
            sqlString = "Select * From PIN WHERE "
            Dim many As Boolean = False
            If TenPin <> "" Then
                sqlString += "TenPin Like '%" + TenPin.ToString() + "%' "
                If many = False Then
                    many = True
                End If
            End If
            If HangSx <> "" Then
                If many = True Then
                    sqlString += " and "

                End If
                If many = False Then
                    many = True
                End If
                sqlString = sqlString + " HangSanXuat Like '%" + HangSx.ToString() + "%' "
                If many = False Then
                    many = True
                End If
            End If
            If MaLoai <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "MaLoai Like '%" + MaLoai.ToString() + "%' "
                If many = False Then
                    many = True
                End If
            End If
            If ImpMin <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "Imp >= '" + ImpMin.ToString() + "'"
                If many = False Then
                    many = True
                End If
            End If
            If ImpMax <> 0 Then

                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "Imp <= '" + ImpMax.ToString() + "'"
                If many = False Then
                    many = True
                End If
            End If
            If ImpMin <> 0 And ImpMax <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "Imp >= '" + ImpMin.ToString() + "' And Imp <= '" + ImpMax.ToString() + "' "
                If many = False Then
                    many = True
                End If
            End If
            If VmpMin <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "Vmp >= '" + VmpMin.ToString() + "'"
                If many = False Then
                    many = True
                End If
            End If
            If VmpMax <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "Vmp <= '" + VmpMax.ToString() + "'"
                If many = False Then
                    many = True
                End If
            End If
            If VmpMin <> 0 And VmpMax <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "Vmp <= '" + VmpMax.ToString() + "' And DienAp >= '" + VmpMin.ToString() + "' "
                If many = False Then
                    many = True
                End If
            End If
            If IscMin <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "Isc >= '" + IscMin.ToString() + "'"
                If many = False Then
                    many = True
                End If
            End If
            If IscMax <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "Isc <= '" + IscMax.ToString() + "'"
                If many = False Then
                    many = True
                End If
            End If
            If IscMin <> 0 And IscMax <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "Gia <= '" + IscMax.ToString() + "' And Gia >= '" + IscMin.ToString() + "' "
                If many = False Then
                    many = True
                End If
            End If
            If VocMin <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "Vsc >= '" + VocMin.ToString() + "'"
                If many = False Then
                    many = True
                End If
            End If
            If VocMax <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "Voc <= '" + VocMax.ToString() + "'"
                If many = False Then
                    many = True
                End If
            End If
            If VocMin <> 0 And VocMax <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "Voc <= '" + VocMax.ToString() + "' And Voc >= '" + VocMin.ToString() + "' "
                If many = False Then
                    many = True
                End If
            End If
        End If

        'sqlString = "Select * From ACQUY WHERE MaAcQuy Like '%" + MAcQuy + "%' or TenAcQuy Like '" + TAcQuy + "' or HangSanXuat Like '" + HSX + "' or MaLoai Like '" + MLoai + "' or '" + DAp + "' "
        Dim lpin As PinDTO
        Dim list As New List(Of PinDTO)
        Dim dt As New System.Data.DataTable
        dt = ketnoi.GetData(sqlString)
        Dim i As Integer
        For i = 0 To dt.Rows.Count - 1
            MaPin = Val(dt.Rows(i)("MaPin"))
            TenPin = dt.Rows(i)("TenPin").ToString
            MaLoai = Val(dt.Rows(i)("MaLoai"))
            HangSx = dt.Rows(i)("HangSanXuat").ToString
            CongSuat = dt.Rows(i)("CongSuat").ToString
            KichThuoc = dt.Rows(i)("KichThuoc").ToString
            Imp = Val(dt.Rows(i)("Imp"))
            Vmp = Val(dt.Rows(i)("Vmp"))
            Isc = Val(dt.Rows(i)("Isc"))
            Voc = Val(dt.Rows(i)("Voc"))
            lpin = New PinDTO(MaPin, TenPin, MaLoai, CongSuat, KichThuoc, HangSx, Imp, Vmp, Isc, Voc)
            list.Add(lpin)
        Next
        Return list
    End Function
    Public Function SelectByID(ByVal id As Integer)
        Dim MaPin As Integer
        Dim TenPin As String
        Dim MaLoai As Integer
        Dim HangSX As String
        Dim CongSuat As Integer
        Dim KichThuoc As String
        Dim Imp As Integer
        Dim Vmp As Integer
        Dim Isc As Integer
        Dim Voc As Integer
        Dim lpin As New PinDTO
        Dim lpinList As New List(Of PinDTO)
        Dim dt As System.Data.DataTable
        dt = ketnoi.GetData("Select * From PIN Where MaPin = " + id.ToString() + "")
        Dim i As Integer
        For i = 0 To dt.Rows.Count - 1
            MaPin = Val(dt.Rows(i)("MaPin"))
            TenPin = dt.Rows(i)("TenPin").ToString
            MaLoai = Val(dt.Rows(i)("MaLoai"))
            HangSX = dt.Rows(i)("HangSanXuat").ToString
            KichThuoc = dt.Rows(i)("KichThuoc").ToString
            Imp = Val(dt.Rows(i)("Imp"))
            Vmp = Val(dt.Rows(i)("Vmp"))
            Isc = Val(dt.Rows(i)("Isc"))
            Voc = Val(dt.Rows(i)("Voc"))
            lpin = New PinDTO(MaPin, TenPin, MaLoai, CongSuat, KichThuoc, HangSX, Imp, Vmp, Isc, Voc)
            lpinList.Add(lpin)
        Next
        Return lpinList
    End Function
End Class
